/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yunhoo.deptBudget.bean;

import com.yunhoo.base.model.Department;
import com.yunhoo.deptBudget.model.BudgetStats;
import com.yunhoo.deptBudget.model.Pzxx;
import com.yunhoo.sys.db.util.IbatisTemplate;
import com.yunhoo.sys.util.common.CommonParam;
import com.yunhoo.sys.util.common.LoginParam;
import com.yunhoo.sys.util.common.Null;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.faces.event.ValueChangeEvent;
import javax.faces.model.SelectItem;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CreationHelper;

@ManagedBean(name = "finPerformanceBean")
@SessionScoped
public class FinPerformanceBean {

    private BudgetStats per = new BudgetStats();
    private List<BudgetStats> modelList = new ArrayList<BudgetStats>();
    private List<BudgetStats> modelForList = new ArrayList<BudgetStats>();
    private List<SelectItem> yearli = null;
    private List<SelectItem> monthList = null;
    private String iyear;
    private Integer endMonth = null;
    private Department currentDept = null;
    private List<Pzxx> pzxxli = null;//凭证信息

    public FinPerformanceBean() {
        init();
    }

    private void init() {
        //初始化年度列表
        yearli = CommonParam.yearList();
        monthList = new ArrayList<SelectItem>();
        for (int i = 1; i <= 12; i++) {
            monthList.add(new SelectItem(i, i + "月"));
        }
        currentDept = LoginParam.getDept();
    }

    public void yearChange(ValueChangeEvent value) {
        iyear = (String) value.getNewValue();
    }

    public void selectFinPerListFor() {
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("iyear", iyear);
        map.put("endMonth", endMonth);
        modelList = IbatisTemplate.queryForList("deptBudgetStats.queryListBudgetExec", map);
        if (!Null.check(modelList)) {
            BudgetStats dbm = modelList.get(0);
            for (int i = 1; i < modelList.size(); i++) {
                if (dbm.getDeptCode().trim().equals(modelList.get(i).getDeptCode().trim())) {
                    modelList.get(i).setDeptName("");
                } else {
                    dbm = modelList.get(i);
                }
            }
        }
    }

    private String getOrgCode() {
        return LoginParam.getOrg().getCode();
    }

    public void showDetail_pubCost(BudgetStats row) {
        if (row.getDeptCode() == null || row.getDeptCode().isEmpty() || endMonth == null) {
            pzxxli = null;
            return;
        }
        String projCode = null;
        if (row.getProjCode() != null && !row.getProjCode().trim().isEmpty()) {
            projCode = "select configCode from pay_R9_config_pro where projCode = "
                    + "'" + row.getProjCode().trim() + "'"
                    + " and orgCode = "
                    + "'" + LoginParam.getOrg().getCode().trim() + "'"
                    + " and iyear = " + LoginParam.getIyear();
        }
        Map map = new HashMap();
        map.put("bmdm", row.getDeptCode());
        map.put("kmdm", projCode);
        map.put("iyear", LoginParam.getIyear());
        map.put("month_begin", 1);
        map.put("month_end", endMonth);
        map.put("orgCode",LoginParam.getOrg().getCode());
        pzxxli = IbatisTemplate.queryForList("deptBudgetStats.queryListPzxx", map);
    }

    public void selectFinPerListForDept() {
        Double budget = 0d;
        Double cost = 0d;
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("iyear", iyear);
        map.put("endMonth", endMonth);
        map.put("deptCode", currentDept.getCode().trim());
        modelForList = IbatisTemplate.queryForList("deptBudgetStats.queryListBudgetExec", map);

        if (!Null.check(modelForList)) {
            for (BudgetStats dbm : modelForList) {
                budget += dbm.getBudget();
                cost += dbm.getCost();
            }
            BudgetStats total = new BudgetStats();
            total.setDeptCode(currentDept.getCode());
            total.setDeptName(currentDept.getName());
            total.setBudget(budget);
            total.setCost(cost);
            modelForList.add(0, total);
            modelForList.get(0).setProjName("合计");
        }
    }

    public void exportExcel() {
        if (modelList != null && modelList.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("部门项目预算执行表");
            String[] excelColumns = initTabeleHeader();
            List<BudgetStats> rows = modelList;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("部门项目预算执行表");
            HSSFRow headerRow = demoSheet.createRow(1);
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns[i]);
            }
            //填充数据
            int rowIndex = 2;
            for (BudgetStats row : rows) {
                createTableRow(demoWorkBook, demoSheet, row, rowIndex, iyear);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeader() {
        String[] excelColumns = {"部门", "内容及工作量", "预算", "执行金额", "进度", "剩余金额"};
        return excelColumns;
    }

    public static void createTableRow(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, BudgetStats buds, int rowIndex, String iyear) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCellStyle dstyled = demoWorkBook.createCellStyle();
        dstyled.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        HSSFFont font = demoWorkBook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setFont(font);//选择需要用到的字体格式

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 5));
        cell1 = row1.createCell(0);
        cell1.setCellValue(iyear + "年度-部门项目预算执行表");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(Null.convert(buds.getDeptName()));
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(buds.getProjName()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(buds.getBudget()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(buds.getCost()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(4);
        if (!Null.check(buds.getBudget())) {
            cell.setCellValue(Null.convert(buds.getCost() / buds.getBudget()));
            cell.setCellStyle(dstyled);
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(5);
        cell.setCellValue(Null.convert(buds.getBudget() - buds.getCost()));
        cell.setCellStyle(dstyle);

    }

    public void exportExcelDept() {
        if (modelForList != null && modelForList.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("部门项目预算执行表");
            String[] excelColumns = initTabeleHeaderDept();
            List<BudgetStats> rows = modelForList;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("部门项目预算执行表");
            HSSFRow headerRow = demoSheet.createRow(1);
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns[i]);
            }
            //填充数据
            int rowIndex = 2;
            for (BudgetStats row : rows) {
                createTableRowDept(demoWorkBook, demoSheet, row, rowIndex, iyear);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeaderDept() {
        String[] excelColumns = {"部门", "内容及工作量", "预算", "执行金额", "进度", "剩余金额"};
        return excelColumns;
    }

    public static void createTableRowDept(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, BudgetStats buds, int rowIndex, String iyear) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCellStyle dstyled = demoWorkBook.createCellStyle();
        dstyled.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        HSSFFont font = demoWorkBook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setFont(font);//选择需要用到的字体格式

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 5));
        cell1 = row1.createCell(0);
        cell1.setCellValue(iyear + "年度-部门项目预算执行表");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        if (Null.check(buds.getProjName())) {
            cell.setCellValue(Null.convert(buds.getDeptName()));
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(buds.getProjName()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(buds.getBudget()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(buds.getCost()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(4);
        if (!Null.check(buds.getBudget())) {
            cell.setCellValue(Null.convert(buds.getCost() / buds.getBudget()));
            cell.setCellStyle(dstyled);
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(5);
        cell.setCellValue(Null.convert(buds.getBudget() - buds.getCost()));
        cell.setCellStyle(dstyle);

    }

    public static void exportExcel(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet) {
        try {
            String strfileName = "book.xls";
            FileOutputStream fos = null;
            //导出
            demoSheet.setGridsPrinted(true);//demoSheet
            HSSFFooter footer = demoSheet.getFooter();
            footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
            // 获得JSF上下文环境
            FacesContext context = FacesContext.getCurrentInstance();
            // 获得ServletContext对象
            ServletContext servletContext = (ServletContext) context.getExternalContext().getContext();
            // 取得文件的绝对路径
            String excelName = servletContext.getRealPath("/upload") + "/" + strfileName;//strfileName
            File exportFile = new File(excelName);
            fos = new FileOutputStream(exportFile);//fos
            demoWorkBook.write(fos);//demoWorkBook
            fos.close();

            FacesContext ctx = FacesContext.getCurrentInstance();
            ctx.responseComplete();
            HttpServletResponse httpServletResponse = (HttpServletResponse) ctx.getExternalContext().getResponse();
            ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + strfileName);//strfileName
            httpServletResponse.setContentLength((int) exportFile.length());//exportFile
            httpServletResponse.setContentType("application/x-download");
            //httpServletResponse.setContentType("application/vnd.ms-excel");
            byte[] b = new byte[1024];
            int i = 0;
            FileInputStream fis = new java.io.FileInputStream(exportFile);
            while ((i = fis.read(b)) > 0) {
                servletOutputStream.write(b, 0, i);
            }
            servletOutputStream.flush();
            ctx.responseComplete();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public BudgetStats getPer() {
        return per;
    }

    public void setPer(BudgetStats per) {
        this.per = per;
    }

    public List<BudgetStats> getModelList() {
        return modelList;
    }

    public void setModelList(List<BudgetStats> modelList) {
        this.modelList = modelList;
    }

    public List<BudgetStats> getModelForList() {
        return modelForList;
    }

    public void setModelForList(List<BudgetStats> modelForList) {
        this.modelForList = modelForList;
    }

    public List<SelectItem> getYearli() {
        return yearli;
    }

    public void setYearli(List<SelectItem> yearli) {
        this.yearli = yearli;
    }

    public List<SelectItem> getMonthList() {
        return monthList;
    }

    public void setMonthList(List<SelectItem> monthList) {
        this.monthList = monthList;
    }

    public String getIyear() {
        return iyear;
    }

    public void setIyear(String iyear) {
        this.iyear = iyear;
    }

    public Integer getEndMonth() {
        return endMonth;
    }

    public void setEndMonth(Integer endMonth) {
        this.endMonth = endMonth;
    }

    public Department getCurrentDept() {
        return currentDept;
    }

    public void setCurrentDept(Department currentDept) {
        this.currentDept = currentDept;
    }

    public List<Pzxx> getPzxxli() {
        return pzxxli;
    }

    public void setPzxxli(List<Pzxx> pzxxli) {
        this.pzxxli = pzxxli;
    }
}
